package bd;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Consultas {
	
	public static double promedioCuarto(String cuarto, String comienzo, String fin){
		double aux=-1;
		
		try{
			//String query="select Count(*)/(TO_DATE(?, 'dd/mm/yyyy') - TO_DATE(?, 'dd/mm/yyyy')) from Reserva where NDormitorio = ? AND ((TO_DATE(?, 'dd/mm/yyyy') >= Arribo AND TO_DATE(?, 'dd/mm/yyyy') <= Partida) OR (TO_DATE(?, 'dd/mm/yyyy') >= Arribo AND TO_DATE(?,'dd/mm/yyyy') <= Partida))";
			String str="select roomAverage(TO_NUMBER(?),TO_DATE(?,'dd/mm/yyyy'),TO_DATE(?,'dd/mm/yyyy')) from dual";
			PreparedStatement stmt = DataBaseConection.conn.prepareStatement(str);
//			stmt.setString(1, fin);
//			stmt.setString(2, comienzo);
//			stmt.setInt(3, Integer.parseInt(cuarto));
//			stmt.setString(4, comienzo);
//			stmt.setString(5, comienzo);
//			stmt.setString(6, fin);
//			stmt.setString(7, fin);
			stmt.setString(1, cuarto);
			stmt.setString(2, comienzo);
			stmt.setString(3, fin);
			ResultSet result = stmt.executeQuery();
			result.next();	
			aux= result.getDouble(1);
			stmt.close();
		} catch(SQLException e){
			System.out.println(e);
		}
		
		return aux;
	}

	public static int ocupacionHostal(String comienzo, String fin) {
		
		int aux=-1;
		
		try{
			String query="select count(*) from Reserva where (Arribo >= TO_DATE(?, 'dd/mm/yyyy') AND Arribo <= TO_DATE(?, 'dd/mm/yyyy')) OR (Partida >= TO_DATE(?,'dd/mm/yyyy') AND Partida <= TO_DATE(?, 'dd/mm/yyyy')) OR (Arribo < TO_DATE(?,'dd/mm/yyyy') AND Partida > TO_DATE(?, 'dd/mm/yyyy'))";
			PreparedStatement stmt = DataBaseConection.conn.prepareStatement(query);
			stmt.setString(1, comienzo);
			stmt.setString(2, fin);
			stmt.setString(3, comienzo);
			stmt.setString(4, fin);
			stmt.setString(5, comienzo);
			stmt.setString(6, fin);
			ResultSet result = stmt.executeQuery();
			result.next();
			aux= result.getInt(1);
			stmt.close();
		}catch(SQLException e){
			System.out.println(e);
		}
		
		return aux;
	}
	
	public static int cuartoMasSolicitado(String mes, String ano) {
		
		int aux=-1;
		
		try{
			//String query="select NDormitorio from Reserva where Count(NDormitorio) = (select Max(Count(NDormitorio)) from Reserva where (year(Arribo) = year(Partida) and year(Arribo)="+ano+" and month(Arribo) <= "+mes+" and month(Partida) >= "+mes+")) and (year(Arribo)<="+ano+" and year(Partida)>="+ano+"and(month(Arribo)<="+mes+" or month(Arribo)<="+mes+"))";
			String str="select mostRequestRoom(?,?) from dual";
			PreparedStatement stmt = DataBaseConection.conn.prepareStatement(str);
			stmt.setString(1, mes);
			stmt.setString(2, ano);
			ResultSet result = stmt.executeQuery();
			result.next();
			aux= result.getInt(1);
			stmt.close();
		}catch(SQLException e){
			System.out.println(e);
		}
		
		return aux;
	}

	public static List<String> cuartosSolicitados(String comienzo, String fin) {
		List<String> lista=new ArrayList<String>();
		
		try{
			String query="select DISTINCT NDormitorio from Reserva where (Arribo >= TO_DATE(?, 'dd/mm/yyyy') AND Arribo <= TO_DATE(?, 'dd/mm/yyyy')) OR (Partida >= TO_DATE(?,'dd/mm/yyyy') AND Partida <= TO_DATE(?, 'dd/mm/yyyy')) OR (Arribo < TO_DATE(?,'dd/mm/yyyy') AND Partida > TO_DATE(?, 'dd/mm/yyyy'))";
			PreparedStatement stmt = DataBaseConection.conn.prepareStatement(query);
			stmt.setString(1, comienzo);
			stmt.setString(2, fin);
			stmt.setString(3, comienzo);
			stmt.setString(4, fin);
			stmt.setString(5, comienzo);
			stmt.setString(6, fin);
			ResultSet result = stmt.executeQuery();
			while(result.next()) {
				lista.add(""+result.getInt("NDormitorio"));
			}
			stmt.close();
		}catch(SQLException e){
			System.out.println(e);
		}
		
		return lista;
	}
	
	public static List<Reserva> reservasHuesped(String tipo, String nro) {
		List<Reserva> lista=new ArrayList<Reserva>();
		
		try{
			String query = "select * from Reserva where NDocumento = TO_NUMBER(?) AND TipoDoc = ?";
			PreparedStatement prep = DataBaseConection.conn.prepareStatement(query);
			prep.setString(1, nro);
			prep.setString(2, tipo);
			ResultSet result = prep.executeQuery();
			while(result.next()) {
				lista.add(new Reserva(result.getInt("Numero"),result.getInt("NDocumento"),result.getString("TipoDoc"),result.getInt("NDormitorio"),result.getDate("Arribo"),result.getDate("Partida"),result.getDouble("PrecioTotal")));
			}
			prep.close();

		}catch(Exception e){
			System.out.println(e);
		}
		
		return lista;
	}
	
	public static List<Cliente> huespedes(String forma) {
		List<Cliente> lista=new ArrayList<Cliente>();
		
		try{
			String aux;

			if(forma.toUpperCase().equals("D") || forma.toUpperCase().equals("DOCUMENTO"))
				aux="NDocumento";
			else if(forma.toUpperCase().equals("A") || forma.toUpperCase().equals("APELLIDO"))
				aux="Apellido";
			else if(forma.toUpperCase().equals("N") || forma.toUpperCase().equals("NACIMIENTO") || forma.toUpperCase().equals("FECHA DE NACIMIENTO"))
				aux="FechaNac";
			else
				return null; //o algo de error, no se
				
			Statement stmt = DataBaseConection.conn.createStatement();
			ResultSet result = stmt.executeQuery("select * from Cliente order by "+aux);
			while(result.next()) {
				lista.add(new Cliente(result.getInt("NDocumento"),result.getString("TipoDoc"),result.getString("Nombre"),result.getString("Apellido"),result.getDate("FechaNac"),result.getString("Sexo"),result.getInt("Telefono"),result.getString("Email")));
			}
			stmt.close();
		}catch(SQLException e){
			System.out.println(e);
		}
		
		return lista;
	}
	
	public static List<String> obtenerServiciosDelCuarto(String cuarto) {
		List<String> lista=new ArrayList<String>();
		
		try{
			String query="select NombreServ from ServiciosDormi where NDormitorio=TO_NUMBER(?)";
			PreparedStatement stmt = DataBaseConection.conn.prepareStatement(query);
			stmt.setString(1, cuarto);
			ResultSet result = stmt.executeQuery();
			while(result.next()) {
				lista.add(result.getString("NombreServ"));
			}
			stmt.close();
		}catch(SQLException e) {
			System.out.println(e);
		}
		
		return lista;
	}
	
	public static List<Dormitorio> obtenerCuartos() {

		List<Dormitorio> dorms = new ArrayList<Dormitorio>();

		try{
			Statement stmt = DataBaseConection.conn.createStatement();
			ResultSet result = stmt.executeQuery("select * from Dormitorios");
			while(result.next()){
				dorms.add(new Dormitorio(result.getInt("NDormitorio"),result.getString("Estado"),result.getString("SexoAdmit"),result.getInt("Cantidad")));
			}
			stmt.close();
		}catch(SQLException e){
			System.out.println(e);
		}

		return dorms;
	}
	
	public static List<Dormitorio> cuartosConServicio(String servicio) {

		List<Dormitorio> dorms = new ArrayList<Dormitorio>();

		try{
			String query="select Dormitorios.NDormitorio, Estado, SexoAdmit, Cantidad from Dormitorios, ServiciosDormi where Dormitorios.NDormitorio=ServiciosDormi.NDormitorio and ServiciosDormi.NombreServ=CAST(? AS CHAR(10))";
			PreparedStatement stmt = DataBaseConection.conn.prepareStatement(query);
			stmt.setString(1, servicio);
			ResultSet result = stmt.executeQuery();
			while(result.next()){
				dorms.add(new Dormitorio(result.getInt("NDormitorio"),result.getString("Estado"),result.getString("SexoAdmit"),result.getInt("Cantidad")));
			}
			stmt.close();
		}catch(SQLException e){
			System.out.println(e);
		}

		return dorms;
	}
	
	public static Dormitorio caracteristicasCuarto(String nro) {

		Dormitorio dorm=null;

		try{
			String query="select * from Dormitorios where NDormitorio=TO_NUMBER(?)";
			PreparedStatement stmt = DataBaseConection.conn.prepareStatement(query);
			stmt.setString(1, nro);
			ResultSet result = stmt.executeQuery();
			if(result.next()){
				dorm=new Dormitorio(result.getInt("NDormitorio"),result.getString("Estado"),result.getString("SexoAdmit"),result.getInt("Cantidad"));
			}
			stmt.close();
		}catch(SQLException e){
			System.out.println(e);
		}

		return dorm;
	}
	
	public static Cliente existeCliente(String doc, String tipo) {

		Cliente cliente=null;
		try{
			String query="select * from Cliente where NDocumento=TO_NUMBER(?) and TipoDoc=?";
			PreparedStatement stmt = DataBaseConection.conn.prepareStatement(query);
			stmt.setString(1, doc);
			stmt.setString(2,tipo);
			ResultSet result = stmt.executeQuery();
			if(result.next())
				cliente = new Cliente(result.getInt("NDocumento"),result.getString("TipoDoc"),result.getString("Nombre"),result.getString("Apellido"),result.getDate("FechaNac"),result.getString("Sexo"),result.getInt("Telefono"),result.getString("Email"));
			stmt.close();
		}catch(SQLException e){
			System.out.println(e);
		}
		
		return cliente;
	}
	
	public static List<String> obtenerServicios() {

		List<String> servicios = new ArrayList<String>();

		try{
			Statement stmt = DataBaseConection.conn.createStatement();
			ResultSet result = stmt.executeQuery("select * from Caracteristicas");
			while(result.next()){
				servicios.add(result.getString("NombreServ")+result.getInt("Precio"));
			}
			stmt.close();
		}catch(SQLException e){
			System.out.println(e);
		}

		return servicios;
	}
	
	public static List<Integer> obtenerCuartos(String arribo, String partida, String sexAdmit, String cantPer) {

		List<Integer> dorms = new ArrayList<Integer>();

		try{
			String query = "SELECT * FROM TABLE (CAST(checkRoomsAvailable(?,?,?,?) AS AvailableDormisTable))";
			PreparedStatement prep = DataBaseConection.conn.prepareStatement(query);
			prep.setString(1, arribo);
			prep.setString(2, partida);
			prep.setString(3, sexAdmit);
			prep.setString(4, cantPer);

			ResultSet result = prep.executeQuery();
			while(result.next()){
				dorms.add(result.getInt("nroDorType"));
			}
			prep.close();
		}catch(SQLException e){
			System.out.println(e);
		}

		return dorms;
	}
	
	public static List<String> historial() {

		List<String> reservas = new ArrayList<String>();

		try{
			Statement stmt = DataBaseConection.conn.createStatement();
			ResultSet result = stmt.executeQuery("select * from Reserva");
			while(result.next()){
				reservas.add(""+result.getInt("Numero")+"        "+result.getInt("NDocumento")+"     "+result.getString("TipoDoc")+
						"       "+result.getInt("NDormitorio")+"          "+result.getDate("Arribo")+"  "+result.getDate("Partida")+
						"   "+result.getDouble("PrecioTotal"));
			}
			stmt.close();
		}catch(SQLException e){
			System.out.println(e);
		}

		return reservas;
	}
	
	public static List<String> disponibilidadDormitorios(String tipo, String comienzo, String fin){
		List<String> lista=new ArrayList<String>();

		try{
			tipo = tipo.toUpperCase();
			String query="select * from TABLE (CAST(getRoomDisponibility(?,?,CAST(? AS CHAR(1)) ) AS dispDormiTable))";
			PreparedStatement stmt=DataBaseConection.conn.prepareStatement(query);
			stmt.setString(1, comienzo);
			stmt.setString(2, fin);
			stmt.setString(3, tipo);
			ResultSet result = stmt.executeQuery();
			while(result.next()) {
				lista.add(""+result.getInt("auxDormi")+"     "+result.getInt("disp")+"      "+result.getInt("now")+"      "+result.getString("bath"));
			}
			stmt.close();
		}catch(SQLException e) {
			System.out.println(e);
		}
		return lista;
	}
}
